﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp
{
    public class DataTableHelper
    {
        // 需要同时检查DataTable是否为null以及它是否包含任何行(dataTable.Rows.Count)，以避免在null对象上调用方法或属性时引发NullReferenceException异常
        
        
        //static void dn()
        //{
        //    // 创建一个 DataTable
        //    DataTable dt = new DataTable();
        //    dt.Columns.Add("Name", typeof(string));
        //    dt.Columns.Add("Age", typeof(int));
        //    dt.Rows.Add("John Doe", 25);
        //    dt.Rows.Add("Jane Doe", 30);
        //    dt.Rows.Add("Mike Smith", 35);

        //        // 定义一个 Person 类
        //        public class Person
        //        {
        //            public string Name { get; set; }
        //            public int Age { get; set; }
        //        }

        //        // 使用 LINQ 将 DataTable 的行转换为 List<Person>
        //        List<Person> list = dt.AsEnumerable().Select(row => new Person { Name = row["Name"].ToString(), Age = int.Parse(row["Age"].ToString()) }).ToList();

        //        // 输出 List 中的数据
        //        foreach (Person person in list)
        //        {
        //            Console.WriteLine($"Name: {person.Name}, Age: {person.Age}");
        //        }
        //}

        public void Demo()
        {
            //IEnumerable<DataRow> QOld = vDTOld.AsEnumerable().ToList();
            ////比较两个数据源的交集
            //IEnumerable<DataRow> QJJ = vDTOld.AsEnumerable().Intersect(DTNew.AsEnumerable(), DataRowComparer.Default);
            ////两个数据源的交集集合      
            //DataTable DTJJ = QJJ.CopyToDataTable();


            ////获取两个数据源的差集
            //IEnumerable<DataRow> QCJ = vDTOld.AsEnumerable().Except(DTNew.AsEnumerable(), DataRowComparer.Default);
            ////两个数据源的差集集合
            //DataTable DTCJ = QCJ.CopyToDataTable();


            //获取两个数据源的并集
            //IEnumerable<DataRow> QBJ = vDTOld.AsEnumerable().Union(DTNew.AsEnumerable(), DataRowComparer.Default);
            //两个数据源的并集集合
            //DataTable DTBJ = QBJ.CopyToDataTable();

        }


        public static void demo1()
        {
            var ds = new DataSet();
            var dt1 = new DataTable();
            var dt2 = new DataTable();

            dt1.Columns.Add("id", typeof(int));
            dt1.Columns.Add("name", typeof(string));
            dt1.Rows.Add(1, "张三");
            dt1.Rows.Add(2, "李四");
            dt1.Rows.Add(3, "王五");

            dt2.Columns.Add("id", typeof(int));
            dt2.Columns.Add("name", typeof(string));
            dt2.Rows.Add(1, "张三");
            dt2.Rows.Add(2, "王五");
            dt2.Rows.Add(4, "赵六");

            // 1. 使用Linq比较数据差异化
            // 将DataTable转换为IEnumerable数据格式
            var query1 = dt1.AsEnumerable();
            var query2 = dt2.AsEnumerable();

            // 获取差异化结果
            var exceptAB = query1.Except(query2, DataRowComparer.Default);
            var exceptBA = query2.Except(query1, DataRowComparer.Default);

            // 2.使用DataView比较数据差异化
            DataView view1 = new DataView(dt1);
            DataView view2 = new DataView(dt2);

            // 获取差异化结果
            view1.RowFilter = "NOT EXISTS(SELECT * FROM dt2 WHERE dt2.id=view1.id)";
            view2.RowFilter = "NOT EXISTS(SELECT * FROM dt1 WHERE dt1.id=view2.id)";

            // 3.使用DataSet的Merge方法比较数据差异化
            ds.Merge(dt1);  // 将dt1导入ds中，如果ds已经包含dt1结构，会合并数据
            ds.Merge(dt2);  // 将dt2导入ds中，如果ds已经包含dt2结构，会合并数据

            // 查询符合条件的单条记录
            var row = dt1.Select("id = 1 and name= '张三'").FirstOrDefault();

            // 输出查询结果
            if (row != null)
            {
                Console.WriteLine("{0} {1}", row["id"], row["name"]);
            }

            var diff = ds.Tables[0].GetChanges();  // 获取差异化结果
            if (diff != null)
            {
                foreach (DataRow dr in diff.Rows)
                {
                    Console.WriteLine(dr["id"] + " " + dr["name"]);
                }
            }
        }

        public static void diff()
        {
            // 创建两个 DataTable
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();

            // 为两个表添加一些数据
            dt1.Columns.Add("ID", typeof(int));
            dt1.Columns.Add("Name", typeof(string));
            for (int i = 1; i < 5; i++)
            {
                dt1.Rows.Add(i, "Row" + i);
            }
            dt2.Columns.Add("ID", typeof(int));
            dt2.Columns.Add("Name", typeof(string));
            for (int i = 0; i < 4; i++)
            {
                dt2.Rows.Add(i, "Row" + i);
            }

            var dic = GetChanges(dt1, dt2, "Name");
            if (dic["insert"] != null)
            {
                foreach (var item in dic["insert"])
                {
                    Console.WriteLine("insert: " + item["Name"]);
                }
            }
            if (dic["update"] != null)
            {
                foreach (var item in dic["update"])
                {
                    Console.WriteLine("update: " + item["Name"]);
                }
            }
            if (dic["delete"] != null)
            {
                foreach (var item in dic["delete"])
                {
                    Console.WriteLine("delete: " + item["Name"]);
                }
            }
        }


        public static Dictionary<string, List<DataRow>> GetChanges(DataTable dtNew, DataTable dtOld, string masterKeyName, StringComparer masterKeyComparer = null)
        {
            IEqualityComparer<string> comparer = masterKeyComparer ?? StringComparer.Ordinal;
            var newKeyLookup = dtNew.AsEnumerable().ToLookup(r => r.Field<string>(masterKeyName), comparer);
            var oldKeyLookup = dtOld.AsEnumerable().ToLookup(r => r.Field<string>(masterKeyName), comparer);
            var updateRows = new List<DataRow>();
            var insertRows = new List<DataRow>();
            var deleteRows = new List<DataRow>();

            foreach (var x in newKeyLookup)
            {
                var existingRows = oldKeyLookup[x.Key].ToList();
                if (existingRows.Count != 0)
                {
                    var allChangedRows = x.Where(r => !existingRows.Contains(r, DataRowComparer.Default));
                    updateRows.AddRange(allChangedRows);
                }
                if (existingRows.Count == 0)
                {
                    insertRows.AddRange(x);
                }
            }

            foreach (var x in oldKeyLookup)
            {
                if (!newKeyLookup[x.Key].Any())
                {
                    deleteRows.AddRange(x);
                }
            }

            return new Dictionary<string, List<DataRow>>
            {
                {"insert", insertRows},
                {"update", updateRows},
                {"delete", deleteRows},
            };
        }

        ///   <summary>
        ///   比较两个DataTable内容是否相等，先是比数量，数量相等就比内容
        ///   </summary>
        ///   <param name="dtA"> </param>
        ///   <param name="dtB"> </param>
        private bool CompareDataTable(DataTable dtA, DataTable dtB)
        {
            if (dtA.Rows.Count == dtB.Rows.Count)
            {
                return false;
            }

            if (!CompareColumn(dtA.Columns, dtB.Columns)) return false;
            
            // 比内容
            for (var i = 0; i < dtA.Rows.Count; i++)
            {
                for (var j = 0; j < dtA.Columns.Count; j++)
                {
                    if (!dtA.Rows[i][j].Equals(dtB.Rows[i][j]))
                    {
                        return false;
                    }
                }
            }
            return true;
        }

        ///   <summary>
        ///   比较两个字段集合是否名称,数据类型一致
        ///   </summary>
        ///   <param name="dcA"> </param>
        ///   <param name="dcB"> </param>
        ///   <returns> </returns>
        private static bool CompareColumn(DataColumnCollection dcA, DataColumnCollection dcB)
        {
            if (dcA.Count != dcB.Count)
            {
                return false;
            }

            foreach (DataColumn dc in dcA)
            {
                //找相同字段名称
                if (dcB.IndexOf(dc.ColumnName) > -1)
                {
                    //测试数据类型
                    if (dc.DataType != dcB[dcB.IndexOf(dc.ColumnName)].DataType)
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            return true;
        }
    }
}
